# 所有和数据库有关的函数方法
import pymysql
import datetime
# 定义和数据库的连接
conn = pymysql.connect(
    host="rm-2zeyw6kp741tle26feo.mysql.rds.aliyuncs.com",
    port=3306,
    user="root",
    passwd="159357Dgf@",
    db="assistant2",
    charset='utf8',
    cursorclass=pymysql.cursors.DictCursor
)

# 根据用户名查询用户是否存在的函数
def query_user_by_username(username):
    sql = "select * from sys_user where username = %s"
    cur = conn.cursor()
    cur.execute(sql, [username])
    result = cur.fetchone()
    return result

# 根据用户名和密码添加数据的函数
def add_user(username,password):
    sql = "insert into sys_user (username,password) values (%s,%s)"
    cur = conn.cursor()
    cur.execute(sql, [username,password])
    conn.commit()

def update_user(username,password):
    sql = "update sys_user set password = %s where username = %s"
    cur = conn.cursor()
    cur.execute(sql, [password,username])
    conn.commit()


def query_message_by_user_id_from_cbby(user_id):
    sql = "select * from cyber_boyfriend where user_id = %s order by message_time asc"
    cur = conn.cursor()
    cur.execute(sql, [user_id])
    list = cur.fetchall()
    return list

def query_message_by_user_id_from_el(user_id):
    sql = "select * from english_translation where user_id = %s order by message_time asc"
    cur = conn.cursor()
    cur.execute(sql, [user_id])
    list = cur.fetchall()
    return list

def query_message_by_user_id_from_jp(user_id):
    sql = "select * from jp_translation where user_id = %s order by message_time asc"
    cur = conn.cursor()
    cur.execute(sql, [user_id])
    list = cur.fetchall()
    return list

def query_message_by_user_id_from_thai(user_id):
    sql = "select * from thai_translation where user_id = %s order by message_time asc"
    cur = conn.cursor()
    cur.execute(sql, [user_id])
    list = cur.fetchall()
    return list

def query_message_by_user_id_from_tlat(user_id):
    sql = "select * from travel_assistant where user_id = %s order by message_time asc"
    cur = conn.cursor()
    cur.execute(sql, [user_id])
    list = cur.fetchall()
    return list
def add_chat_message_from_cbby(user_id,message,role):
    sql = "insert into cyber_boyfriend (user_id, message, role,message_time) values (%s,%s,%s,%s)"
    cur = conn.cursor()
    cur.execute(sql, [user_id,message,role,datetime.datetime.now()])
    conn.commit()

def add_chat_message_from_el(user_id,message,role):
    sql = "insert into english_translation (user_id, message, role,message_time) values (%s,%s,%s,%s)"
    cur = conn.cursor()
    cur.execute(sql, [user_id,message,role,datetime.datetime.now()])
    conn.commit()

def add_chat_message_from_jp(user_id,message,role):
    sql = "insert into jp_translation (user_id, message, role,message_time) values (%s,%s,%s,%s)"
    cur = conn.cursor()
    cur.execute(sql, [user_id,message,role,datetime.datetime.now()])
    conn.commit()

def add_chat_message_from_thai(user_id,message,role):
    sql = "insert into thai_translation (user_id, message, role,message_time) values (%s,%s,%s,%s)"
    cur = conn.cursor()
    cur.execute(sql, [user_id,message,role,datetime.datetime.now()])
    conn.commit()

def add_chat_message_from_tlat(user_id,message,role):
    sql = "insert into travel_assistant (user_id, message, role,message_time) values (%s,%s,%s,%s)"
    cur = conn.cursor()
    cur.execute(sql, [user_id,message,role,datetime.datetime.now()])
    conn.commit()